<?php

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

class DataLRA {

    private $db;
    private $_deskripsi;
    private $_apbn;
    private $_realisasi_bun;
    private $_realisasi_kppn;
    private $_jumlah;
	private $_persentase;
	private $_flag;
	private $_updownflag;
	private $_apbn_15;
	private $_apbn_14;
	private $_jumlah_15;
	private $_jumlah_14;
	private $_persentase_15;
	private $_persentase_14;
    private $_table1 = 'LRA_APBN';
    
    public $registry;

    /*
     * konstruktor
     */

    
    public function __construct($registry = Registry) {
        $this->db = $registry->db;
        $this->registry = $registry;
    }

    /*
     * mendapatkan data dari tabel Data Tetap
     * @param limit batas default null
     * return array objek Data Tetap */

    public function get_lra_apbn($filter) {
        Session::get('id_user');
        $sql = "SELECT * 
				FROM "
                . $this->_table1 .
				" where 1=1 "
				
				;
        $no = 0;
        foreach ($filter as $filter) {
            $sql .= " AND " . $filter;
        }

        //$sql .= " ORDER BY A.KPPN, A.AKUN ";

        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
			$d_data->set_deskripsi($val['DESCRIPTION']);
            $d_data->set_apbn($val['APBN']);
            $d_data->set_realisasi_bun($val['REALIASI_BUN']);
            $d_data->set_realisasi_kppn($val['REALIASI_KPP']);
            $d_data->set_jumlah($val['JUMALAH']); 
			$d_data->set_persentase($val['PERSENTASE']);
			$d_data->set_flag($val['FLAG']);				
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_lra_harian($filter) {
        Session::get('id_user');
        $sql = " with lra_harian_1 as (
            select 
                 lakb.report_name
            ,      lakb.seq
            ,      lakb.description as description
            ,      lakb.change_sign_flag
            ,      lakb.flag
            ,      lakb.fin_flag
            ,      sum(lakb.apbn) as apbn
            ,      sum(lakb.curr_day_actual_bun) as realiasi_bun
            ,      sum(lakb.curr_day_actual_kpp) as realiasi_kpp
            ,      sum(lakb.curr_day_actual_bun) + sum(lakb.curr_day_actual_kpp) as jumalah
            ,      round(case when sum(lakb.apbn) <> 0 then (sum(lakb.curr_day_actual_bun) + sum(lakb.curr_day_actual_kpp))/sum(lakb.apbn) * 100 
                   else 0 end,2)*100 as persentase
            from  (
           
                   select /*+ full(gjl) full(gcc) full(gjh) */
                          aab.report_name       as report_name
                   ,      aab.axis_seq          as seq
                   ,      aab.description       as description
                   ,      aab.change_sign_flag  as change_sign_flag
                   ,      aab.flag              as flag
                   ,      aab.fin_flag          as fin_flag
                   ,      nvl( sum( decode( aab.sign, '-', -1, 1 )
                                  * decode( gjl.kppn_lra--gcc.segment2
                                          , '999', nvl( rphreal, 0 )
                                          , 0
                                  ))
                             , 0 )              as curr_day_actual_bun
                   ,      nvl( sum( decode( aab.sign, '-', -1, 1 )
                                  * decode( gjl.kppn_lra
                                          , '999', 0
                                          , '000', 0
                                          , ''   , 0
                                          , nvl( rphreal,0 )
                                  ))
                             , 0 )              as curr_day_actual_kpp
                   ,      0                     as apbn
                   from  
                         dm_lra_harian              gjl                 
                   ,      aab
                   where  1=1
                   and    gjl.tglpost           <= trunc(sysdate-1)
                   and    gjl.akun           = aab.segment3
                   and    gjl.bud_type           between aab.segment10_low and aab.segment10_high  "  ;     
                   $no = 0;
					foreach ($filter as $filter) {
						$sql .= " AND " . $filter;
					}
					;
                 $sql .=   "group by aab.report_name
                   ,        aab.axis_seq
                   ,        aab.description
                   ,        aab.change_sign_flag
                   ,        aab.flag
                   ,        aab.fin_flag
                  
                   union all                   
                   select /*+ full(gbl) full(gcc) */
                          aab.report_name       as report_name
                   ,      aab.axis_seq          as seq
                   ,      aab.description       as description
                   ,      aab.change_sign_flag  as change_sign_flag
                   ,      aab.flag              as flag
                   ,      aab.fin_flag          as fin_flag
                   ,      0                     as curr_day_actual_bun
                   ,      0                     as curr_day_actual_kpp
                   ,      nvl( sum( decode( aab.segment10_low
                                          , 7, 0
                                          , decode( aab.sign, '-', -1, 1 )
                                          * ( nvl( jumlah, 0 ) )
                                  ))
                             , 0 )              as apbn
                   from    apbn           gbl                 
                   ,      aab
                   where  1=1
                   and    gbl.akun            = aab.segment3                 
                   and    gbl.tahun        = '2015'
                   group by aab.report_name
                   ,        aab.axis_seq
                   ,        aab.description
                   ,        aab.change_sign_flag
                   ,        aab.flag
                   ,        aab.fin_flag
                 ) lakb
            group by lakb.report_name
            ,        lakb.seq
            ,        lakb.description
            ,        lakb.change_sign_flag
            ,        lakb.flag
            ,        lakb.fin_flag
             ORDER BY lakb.seq 
             )

				select * from lra_harian_1 
				UNION ALL
				--- KESEIMBANGAN PRIMER
				select 'LAPORAN REALISASI APBN' report_name,
				520 AS seq,
				'C. Keseimbanagan Primer' description,
				'N' change_sign_flag,
				'C' Flag,
				'Y' Fin_Flag,
				(select sum(pendapatan_dn-belanja+belanja_hutang) from 
						  (select APBN pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
						  (select APBN belanja FROM lra_harian_1 WHERE seq = '265'), 
						  (select APBN belanja_hutang FROM lra_harian_1 WHERE seq = '315')
				) APBN,
				(select sum(pendapatan_dn-belanja+belanja_hutang) from 
						  (select REALIASI_BUN pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
						  (select REALIASI_BUN belanja FROM lra_harian_1 WHERE seq = '265'), 
						  (select REALIASI_BUN belanja_hutang FROM lra_harian_1 WHERE seq = '315')
				) REALIASI_BUN,
				(select sum(pendapatan_dn-belanja+belanja_hutang) from 
						  (select REALIASI_KPP pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
						  (select REALIASI_KPP belanja FROM lra_harian_1 WHERE seq = '265'), 
						  (select REALIASI_KPP belanja_hutang FROM lra_harian_1 WHERE seq = '315')
				) REALIASI_KPP,
				(select sum(pendapatan_dn-belanja+belanja_hutang) from 
						  (select JUMALAH pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
						  (select JUMALAH belanja FROM lra_harian_1 WHERE seq = '265'), 
						  (select JUMALAH belanja_hutang FROM lra_harian_1 WHERE seq = '315')
				) JUMALAH,
				ROUND((select sum(pendapatan_dn-belanja+belanja_hutang) from 
						  (select JUMALAH pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
						  (select JUMALAH belanja FROM lra_harian_1 WHERE seq = '265'), 
						  (select JUMALAH belanja_hutang FROM lra_harian_1 WHERE seq = '315')
				) /
				(select sum(pendapatan_dn-belanja+belanja_hutang) from 
						  (select APBN pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
						  (select APBN belanja FROM lra_harian_1 WHERE seq = '265'), 
						  (select APBN belanja_hutang FROM lra_harian_1 WHERE seq = '315')
				) * 100,2)*100 PERSENTASE
				FROM
				DUAL
				--- SURPLUS DEFISIT
				UNION ALL
				select 'LAPORAN REALISASI APBN' report_name,
				530 AS seq,
				'D. Surplus/Defisit Anggaran' description,
				'N' change_sign_flag,
				'D' Flag,
				'Y' Fin_Flag,
				(select sum(pendapatan_dn-belanja) from 
						  (select APBN pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
						  (select APBN belanja FROM lra_harian_1 WHERE seq = '265')         
				) APBN,
				(select sum(pendapatan_dn-belanja) from 
						  (select REALIASI_BUN pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
						  (select REALIASI_BUN belanja FROM lra_harian_1 WHERE seq = '265')          
				) REALIASI_BUN,
				(select sum(pendapatan_dn-belanja) from 
						  (select REALIASI_KPP pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
						  (select REALIASI_KPP belanja FROM lra_harian_1 WHERE seq = '265')
				) REALIASI_KPP,
				(select sum(pendapatan_dn-belanja) from 
						  (select JUMALAH pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
						  (select JUMALAH belanja FROM lra_harian_1 WHERE seq = '265')
				) JUMALAH,
				ROUND((select sum(pendapatan_dn-belanja) from 
						  (select JUMALAH pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
						  (select JUMALAH belanja FROM lra_harian_1 WHERE seq = '265')
				) /
				(select sum(pendapatan_dn-belanja) from 
						  (select APBN pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
						  (select APBN belanja FROM lra_harian_1 WHERE seq = '265')
				) * 100,2)*100 PERSENTASE
				FROM
				DUAL
				order by SEQ" 
				
				;
      

        //$sql .= " ORDER BY A.KPPN, A.AKUN ";

        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
			$d_data->set_deskripsi($val['DESCRIPTION']);
            $d_data->set_apbn($val['APBN']);
            $d_data->set_realisasi_bun($val['REALIASI_BUN']);
            $d_data->set_realisasi_kppn($val['REALIASI_KPP']);
            $d_data->set_jumlah($val['JUMALAH']); 
			$d_data->set_persentase(number_format($val['PERSENTASE']/100,2,","," "));
			
			$d_data->set_flag($val['FLAG']);				
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_lra_yoy($filter, $filter2) {
        Session::get('id_user');
        $sql = " with lra_harian_1 as (
            select 
                 lakb.report_name
            ,      lakb.seq
            ,      lakb.description as description
            ,      lakb.change_sign_flag
            ,      lakb.flag
            ,      lakb.fin_flag
            ,      sum(lakb.apbn_15) apbn15
            ,      sum(lakb.curr_day_actual_bun_15) + sum(lakb.curr_day_actual_kpp_15) as jumalah_15
            ,      round(case when sum(lakb.apbn_15) <> 0 then (sum(lakb.curr_day_actual_bun_15) + sum(lakb.curr_day_actual_kpp_15))/sum(lakb.apbn_15) * 100 
                   else 0 end,2)*100 as persentase_15
            ,      sum(lakb.apbn_14) apbn14
            ,      sum(lakb.curr_day_actual_bun_14) + sum(lakb.curr_day_actual_kpp_14) as jumalah_14
            ,      round(case when sum(lakb.apbn_14) <> 0 then (sum(lakb.curr_day_actual_bun_14) + sum(lakb.curr_day_actual_kpp_14))/sum(lakb.apbn_14) * 100 
                   else 0 end,2)*100 as persentase_14
            ,      case when round(case when sum(lakb.apbn_15) <> 0 then (sum(lakb.curr_day_actual_bun_15) + sum(lakb.curr_day_actual_kpp_15))/sum(lakb.apbn_15) * 100 
                   else 0 end,2) > round(case when sum(lakb.apbn_14) <> 0 then (sum(lakb.curr_day_actual_bun_14) + sum(lakb.curr_day_actual_kpp_14))/sum(lakb.apbn_14) * 100 
                   else 0 end,2) then 'UP' else 'DOWN' end UPDOWN_FLAG
            from  (
               
                  select /*+ full(gjl) full(gcc) full(gjh) */
                          aab.report_name       as report_name
                   ,      aab.axis_seq          as seq
                   ,      aab.description       as description
                   ,      aab.change_sign_flag  as change_sign_flag
                   ,      aab.flag              as flag
                   ,      aab.fin_flag          as fin_flag                  
                   ,      nvl( sum( decode( aab.sign, '-', -1, 1 )
                                  * decode( gjl.kppn_lra
                                          , '999', nvl( rphreal, 0 )
                                          , 0
                                  ))
                             , 0 )              as curr_day_actual_bun_14
                   ,      nvl( sum( decode( aab.sign, '-', -1, 1 )
                                  * decode( gjl.kppn_lra
                                          , '999', 0
                                          , '000', 0
                                          , ''   , 0
                                          , nvl( rphreal,0 )
                                  ))
                             , 0 )              as curr_day_actual_kpp_14
                   ,      0                     as apbn_14
                   ,      0                     as curr_day_actual_bun_15
                   ,      0                     as curr_day_actual_kpp_15
                   ,      0                     as apbn_15
                   from  
                         dm_lra_harian14              gjl                 
                   ,      aab
                   where  1=1                   
                   and    gjl.akun           = aab.segment3
                   and    gjl.bud_type           between aab.segment10_low and aab.segment10_high "  ;     
                   $no = 0;
					foreach ($filter as $filter) {
						$sql .= " AND " . $filter;
					}
					;
                 $sql .=   "group by aab.report_name
                   ,        aab.axis_seq
                   ,        aab.description
                   ,        aab.change_sign_flag
                   ,        aab.flag
                   ,        aab.fin_flag
                    
                  union all
              
                   select /*+ full(gjl) full(gcc) full(gjh) */
                          aab.report_name       as report_name
                   ,      aab.axis_seq          as seq
                   ,      aab.description       as description
                   ,      aab.change_sign_flag  as change_sign_flag
                   ,      aab.flag              as flag
                   ,      aab.fin_flag          as fin_flag
                   ,      0                     as curr_day_actual_bun_14
                   ,      0                     as curr_day_actual_kpp_14
                   ,      0                     as apbn_14
                   ,      nvl( sum( decode( aab.sign, '-', -1, 1 )
                                  * decode( gjl.kppn_lra
                                          , '999', nvl( rphreal, 0 )
                                          , 0
                                  ))
                             , 0 )              as curr_day_actual_bun_15
                   ,      nvl( sum( decode( aab.sign, '-', -1, 1 )
                                  * decode( gjl.kppn_lra
                                          , '999', 0
                                          , '000', 0
                                          , ''   , 0
                                          , nvl( rphreal,0 )
                                  ))
                             , 0 )              as curr_day_actual_kpp_15
                   ,      0                     as apbn_15
                   from  
                         dm_lra_harian              gjl                 
                   ,      aab
                   where  1=1
                   and    gjl.akun           = aab.segment3
                   and    gjl.bud_type           between aab.segment10_low and aab.segment10_high " 
				
				;
				//$no = 0;
					foreach ($filter2 as $filter2) {
						$sql .= " AND " . $filter2;
					}
					;
				$sql .= "group by aab.report_name
                   ,        aab.axis_seq
                   ,        aab.description
                   ,        aab.change_sign_flag
                   ,        aab.flag
                   ,        aab.fin_flag
                
                   union all                   
                   select /*+ full(gbl) full(gcc) */
                          aab.report_name       as report_name
                   ,      aab.axis_seq          as seq
                   ,      aab.description       as description
                   ,      aab.change_sign_flag  as change_sign_flag
                   ,      aab.flag              as flag
                   ,      aab.fin_flag          as fin_flag
                   ,      0                     as curr_day_actual_bun_14
                   ,      0                     as curr_day_actual_kpp_14
                   ,      0                     as apbn_14
                   ,      0                     as curr_day_actual_bun_15
                   ,      0                     as curr_day_actual_kpp_15
                   ,      nvl( sum( decode( aab.segment10_low
                                          , 7, 0
                                          , decode( aab.sign, '-', -1, 1 )
                                          * ( nvl( jumlah, 0 ) )
                                  ))
                             , 0 )              as apbn_15
                   from    apbn           gbl                 
                   ,      aab
                   where  1=1
                   and    gbl.akun            = aab.segment3                 
                   and    gbl.tahun        = '2015'
                   group by aab.report_name
                   ,        aab.axis_seq
                   ,        aab.description
                   ,        aab.change_sign_flag
                   ,        aab.flag
                   ,        aab.fin_flag
							 
							   union all                   
							   select /*+ full(gbl) full(gcc) */
									  aab.report_name       as report_name
							   ,      aab.axis_seq          as seq
							   ,      aab.description       as description
							   ,      aab.change_sign_flag  as change_sign_flag
							   ,      aab.flag              as flag
							   ,      aab.fin_flag          as fin_flag
							   ,      0                     as curr_day_actual_bun_14
							   ,      0                     as curr_day_actual_kpp_14
							 
							   ,      nvl( sum( decode( aab.segment10_low
													  , 7, 0
													  , decode( aab.sign, '-', -1, 1 )
													  * ( nvl( jumlah, 0 ) )
											  ))
										 , 0 )              as apbn_14
							   ,      0                     as curr_day_actual_bun_15
							   ,      0                     as curr_day_actual_kpp_15
							   ,      0                     as apbn_15
							   from    apbn_14           gbl                 
							   ,      aab
							   where  1=1
							   and    gbl.akun            = aab.segment3                 
							   and    gbl.tahun        = '2014'
							   group by aab.report_name
							   ,        aab.axis_seq
							   ,        aab.description
							   ,        aab.change_sign_flag
							   ,        aab.flag
							   ,        aab.fin_flag
							   order by seq
							 ) lakb
						group by lakb.report_name
						,        lakb.seq
						,        lakb.description
						,        lakb.change_sign_flag
						,        lakb.flag
						,        lakb.fin_flag
						 ORDER BY lakb.seq 
						 
						 )

			select * from lra_harian_1 
			UNION ALL
		
			select 'LAPORAN REALISASI APBN' report_name,
			520 AS seq,
			'C. Keseimbanagan Primer' description,
			'N' change_sign_flag,
			'C' Flag,
			'Y' Fin_Flag,
			(select sum(pendapatan_dn-belanja+belanja_hutang) from 
					  (select APBN15 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select APBN15 belanja FROM lra_harian_1 WHERE seq = '265'), 
					  (select APBN15 belanja_hutang FROM lra_harian_1 WHERE seq = '315')
			) as apbn15,
			(select sum(pendapatan_dn-belanja+belanja_hutang) from 
					  (select jumalah_15 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select jumalah_15 belanja FROM lra_harian_1 WHERE seq = '265'), 
					  (select jumalah_15 belanja_hutang FROM lra_harian_1 WHERE seq = '315')
			) jumalah_15,
			ROUND((select sum(pendapatan_dn-belanja+belanja_hutang) from 
					  (select jumalah_15 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select jumalah_15 belanja FROM lra_harian_1 WHERE seq = '265'), 
					  (select jumalah_15 belanja_hutang FROM lra_harian_1 WHERE seq = '315')
			) /
			(select sum(pendapatan_dn-belanja+belanja_hutang) from 
					  (select apbn15 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select apbn15 belanja FROM lra_harian_1 WHERE seq = '265'), 
					  (select apbn15 belanja_hutang FROM lra_harian_1 WHERE seq = '315')
			) * 100,2)*100 PERSENTASE_15,
			(select sum(pendapatan_dn-belanja+belanja_hutang) from 
					  (select APBN14 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select APBN14 belanja FROM lra_harian_1 WHERE seq = '265'), 
					  (select APBN14 belanja_hutang FROM lra_harian_1 WHERE seq = '315')
			) as apbn14,
			(select sum(pendapatan_dn-belanja+belanja_hutang) from 
					  (select jumalah_14 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select jumalah_14 belanja FROM lra_harian_1 WHERE seq = '265'), 
					  (select jumalah_14 belanja_hutang FROM lra_harian_1 WHERE seq = '315')
			) jumalah_14,
			ROUND((select sum(pendapatan_dn-belanja+belanja_hutang) from 
					  (select jumalah_14 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select jumalah_14 belanja FROM lra_harian_1 WHERE seq = '265'), 
					  (select jumalah_14 belanja_hutang FROM lra_harian_1 WHERE seq = '315')
			) /
			(select sum(pendapatan_dn-belanja+belanja_hutang) from 
					  (select apbn14 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select apbn14 belanja FROM lra_harian_1 WHERE seq = '265'), 
					  (select apbn14 belanja_hutang FROM lra_harian_1 WHERE seq = '315')
			) * 100,2)*100  PERSENTASE_14,
			CASE WHEN 
			ROUND((select sum(pendapatan_dn-belanja+belanja_hutang) from 
					  (select jumalah_15 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select jumalah_15 belanja FROM lra_harian_1 WHERE seq = '265'), 
					  (select jumalah_15 belanja_hutang FROM lra_harian_1 WHERE seq = '315')
			) /
			(select sum(pendapatan_dn-belanja+belanja_hutang) from 
					  (select apbn15 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select apbn15 belanja FROM lra_harian_1 WHERE seq = '265'), 
					  (select apbn15 belanja_hutang FROM lra_harian_1 WHERE seq = '315')
			) * 100,2) <
			ROUND((select sum(pendapatan_dn-belanja+belanja_hutang) from 
					  (select jumalah_14 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select jumalah_14 belanja FROM lra_harian_1 WHERE seq = '265'), 
					  (select jumalah_14 belanja_hutang FROM lra_harian_1 WHERE seq = '315')
			) /
			(select sum(pendapatan_dn-belanja+belanja_hutang) from 
					  (select apbn14 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select apbn14 belanja FROM lra_harian_1 WHERE seq = '265'), 
					  (select apbn14 belanja_hutang FROM lra_harian_1 WHERE seq = '315')
			) * 100,2) THEN 'UP' ELSE 'DOWN' END UPDOWN_FLAG
			FROM
			DUAL
			
			UNION ALL
			select 'LAPORAN REALISASI APBN' report_name,
			530 AS seq,
			'D. Surplus/Defisit Anggaran' description,
			'N' change_sign_flag,
			'D' Flag,
			'Y' Fin_Flag,
			(select sum(pendapatan_dn-belanja) from 
					  (select APBN15 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select APBN15 belanja FROM lra_harian_1 WHERE seq = '265')         
			) APBN15,
			(select sum(pendapatan_dn-belanja) from 
					  (select jumalah_15 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select jumalah_15 belanja FROM lra_harian_1 WHERE seq = '265')         
			) jumalah_15,
			ROUND((select sum(pendapatan_dn-belanja) from 
					  (select jumalah_15 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select jumalah_15 belanja FROM lra_harian_1 WHERE seq = '265')
			) /
			(select sum(pendapatan_dn-belanja) from 
					  (select apbn15 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select apbn15 belanja FROM lra_harian_1 WHERE seq = '265')
			) * 100,2)*100 PERSENTASE_15,
			(select sum(pendapatan_dn-belanja) from 
					  (select APBN14 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select APBN14 belanja FROM lra_harian_1 WHERE seq = '265')         
			) APBN14,
			(select sum(pendapatan_dn-belanja) from 
					  (select jumalah_14 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select jumalah_14 belanja FROM lra_harian_1 WHERE seq = '265')         
			) jumalah_14,
			ROUND((select sum(pendapatan_dn-belanja) from 
					  (select jumalah_14 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select jumalah_14 belanja FROM lra_harian_1 WHERE seq = '265')
			) /
			(select sum(pendapatan_dn-belanja) from 
					  (select apbn14 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select apbn14 belanja FROM lra_harian_1 WHERE seq = '265')
			) * 100,2)*100  as PERSENTASE_14,
			CASE WHEN 
			ROUND((select sum(pendapatan_dn-belanja) from 
					  (select jumalah_15 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select jumalah_15 belanja FROM lra_harian_1 WHERE seq = '265')
			) /
			(select sum(pendapatan_dn-belanja) from 
					  (select apbn15 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select apbn15 belanja FROM lra_harian_1 WHERE seq = '265')
			) * 100,2) <
			ROUND((select sum(pendapatan_dn-belanja) from 
					  (select jumalah_14 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select jumalah_14 belanja FROM lra_harian_1 WHERE seq = '265')
			) /
			(select sum(pendapatan_dn-belanja) from 
					  (select apbn14 pendapatan_dn FROM lra_harian_1 WHERE seq = '5'), 
					  (select apbn14 belanja FROM lra_harian_1 WHERE seq = '265')
			) * 100,2) THEN 'UP' ELSE 'DOWN' END UPDOWN_FLAG
			FROM
			DUAL
			order by SEQ ";
		//var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
			$d_data->set_deskripsi($val['DESCRIPTION']);
            $d_data->set_apbn_14($val['APBN14']);
			$d_data->set_apbn_15($val['APBN15']);
            $d_data->set_jumlah_14($val['JUMALAH_14']); 
			$d_data->set_jumlah_15($val['JUMALAH_15']); 			
			$d_data->set_persentase_14(number_format($val['PERSENTASE_14']/100,2,","," "));
			$d_data->set_persentase_15(number_format($val['PERSENTASE_15']/100,2,","," "));
			$d_data->set_flag($val['FLAG']);	
			$d_data->set_updownflag($val['UPDOWN_FLAG']);			
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_lra_sub_modul($filter) {
        Session::get('id_user');
        $sql = " with lra_apbn_1 as (
select 
                 lakb.report_name
            ,      lakb.seq
            ,      lakb.description as description
            ,      lakb.change_sign_flag
            ,      lakb.flag
            ,      lakb.fin_flag
            ,      sum(lakb.apbn) as apbn
            ,      sum(lakb.curr_day_actual_bun) as realiasi_bun
            ,      sum(lakb.curr_day_actual_kpp) as realiasi_kpp
            ,      sum(lakb.curr_day_actual_bun) + sum(lakb.curr_day_actual_kpp) as jumalah
            ,      round(case when sum(lakb.apbn) <> 0 then (sum(lakb.curr_day_actual_bun) + sum(lakb.curr_day_actual_kpp))/sum(lakb.apbn) * 100 
                   else 0 end,2) as persentase
            from  (
                   select /*+ full(gjl) full(gcc) full(gjh) */
                          aab.report_name       as report_name
                   ,      aab.axis_seq          as seq
                   ,      aab.description       as description
                   ,      aab.change_sign_flag  as change_sign_flag
                   ,      aab.flag              as flag
                   ,      aab.fin_flag          as fin_flag
                   ,      nvl( sum( decode( aab.sign, '-', -1, 1 )
                                  * decode( gjl.segment2--gcc.segment2
                                          , '999', nvl( amount_idr * -1, 0 )
                                          , 0
                                  ))
                             , 0 )              as curr_day_actual_bun
                   ,      nvl( sum( decode( aab.sign, '-', -1, 1 )
                                  * decode( gjl.segment2
                                          , '999', 0
                                          , '000', 0
                                          , ''   , 0
                                          , nvl( amount_idr * -1,0 )
                                  ))
                             , 0 )              as curr_day_actual_kpp
                   ,      0                     as apbn
                   from   --gl_je_headers            gjh
                         SPGR_SUM_SL              gjl
                   
                   ,      aab
                   where  1=1 
                   and    gjl.segment3           = aab.segment3
                   group by aab.report_name
                   ,        aab.axis_seq
                   ,        aab.description
                   ,        aab.change_sign_flag
                   ,        aab.flag
                   ,        aab.fin_flag
                  union all
                  
                  
                   select /*+ full(gjl) full(gcc) full(gjh) */
                          aab.report_name       as report_name
                   ,      aab.axis_seq          as seq
                   ,      aab.description       as description
                   ,      aab.change_sign_flag  as change_sign_flag
                   ,      aab.flag              as flag
                   ,      aab.fin_flag          as fin_flag
                   ,      nvl( sum( decode( aab.sign, '-', -1, 1 )
                                  * decode( gjl.segment2--gcc.segment2
                                          , '999', nvl( amount, 0 )
                                          , 0
                                  ))
                             , 0 )              as curr_day_actual_bun
                   ,      nvl( sum( decode( aab.sign, '-', -1, 1 )
                                  * decode( gjl.segment2
                                          , '999', 0
                                          , '000', 0
                                          , ''   , 0
                                          , nvl( nvl(base_amount,amount),0 )
                                  ))
                             , 0 )              as curr_day_actual_kpp
                   ,      0                     as apbn
                   from  
                         ap_invoice_distributions_all_v              gjl
                 
                   ,      aab
                   where  1=1 
                   and to_char(accounting_date,'yyyy') = '2015'
                   
                   and check_number is not null
                   
                   and    gjl.segment3           = aab.segment3
                   group by aab.report_name
                   ,        aab.axis_seq
                   ,        aab.description
                   ,        aab.change_sign_flag
                   ,        aab.flag
                   ,        aab.fin_flag
                   

                   union all

                   select /*+ full(gbl) full(gcc) */
                          aab.report_name       as report_name
                   ,      aab.axis_seq          as seq
                   ,      aab.description       as description
                   ,      aab.change_sign_flag  as change_sign_flag
                   ,      aab.flag              as flag
                   ,      aab.fin_flag          as fin_flag
                   ,      0                     as curr_day_actual_bun
                   ,      0                     as curr_day_actual_kpp
                   ,      nvl( sum( decode( aab.segment10_low
                                          , 7, 0
                                          , decode( aab.sign, '-', -1, 1 )
                                          * ( nvl( jumlah, 0 ) )
                                  ))
                             , 0 )              as apbn
                   from    apbn           gbl
                  
                   ,      aab
                   where  1=1 --gbl.code_combination_id = gcc.code_combination_id
                   and    gbl.akun            = aab.segment3
                  
                   and    gbl.tahun        = '2015'
                   group by aab.report_name
                   ,        aab.axis_seq
                   ,        aab.description
                   ,        aab.change_sign_flag
                   ,        aab.flag
                   ,        aab.fin_flag
                 ) lakb
            group by lakb.report_name
            ,        lakb.seq
            ,        lakb.description
            ,        lakb.change_sign_flag
            ,        lakb.flag
            ,        lakb.fin_flag
             ORDER BY lakb.seq )

select * from lra_apbn_1 
UNION ALL
select 'LAPORAN REALISASI APBN' report_name,
520 AS seq,
'C. Keseimbanagan Primer' description,
'N' change_sign_flag,
'C' Flag,
'Y' Fin_Flag,
(select sum(pendapatan_dn-belanja+belanja_hutang) from 
          (select APBN pendapatan_dn FROM lra_apbn_1 WHERE seq = '5'), 
          (select APBN belanja FROM lra_apbn_1 WHERE seq = '265'), 
          (select APBN belanja_hutang FROM lra_apbn_1 WHERE seq = '315')
) APBN,
(select sum(pendapatan_dn-belanja+belanja_hutang) from 
          (select REALIASI_BUN pendapatan_dn FROM lra_apbn_1 WHERE seq = '5'), 
          (select REALIASI_BUN belanja FROM lra_apbn_1 WHERE seq = '265'), 
          (select REALIASI_BUN belanja_hutang FROM lra_apbn_1 WHERE seq = '315')
) REALIASI_BUN,
(select sum(pendapatan_dn-belanja+belanja_hutang) from 
          (select REALIASI_KPP pendapatan_dn FROM lra_apbn_1 WHERE seq = '5'), 
          (select REALIASI_KPP belanja FROM lra_apbn_1 WHERE seq = '265'), 
          (select REALIASI_KPP belanja_hutang FROM lra_apbn_1 WHERE seq = '315')
) REALIASI_KPP,
(select sum(pendapatan_dn-belanja+belanja_hutang) from 
          (select JUMALAH pendapatan_dn FROM lra_apbn_1 WHERE seq = '5'), 
          (select JUMALAH belanja FROM lra_apbn_1 WHERE seq = '265'), 
          (select JUMALAH belanja_hutang FROM lra_apbn_1 WHERE seq = '315')
) JUMALAH,
ROUND((select sum(pendapatan_dn-belanja+belanja_hutang) from 
          (select JUMALAH pendapatan_dn FROM lra_apbn_1 WHERE seq = '5'), 
          (select JUMALAH belanja FROM lra_apbn_1 WHERE seq = '265'), 
          (select JUMALAH belanja_hutang FROM lra_apbn_1 WHERE seq = '315')
) /
(select sum(pendapatan_dn-belanja+belanja_hutang) from 
          (select APBN pendapatan_dn FROM lra_apbn_1 WHERE seq = '5'), 
          (select APBN belanja FROM lra_apbn_1 WHERE seq = '265'), 
          (select APBN belanja_hutang FROM lra_apbn_1 WHERE seq = '315')
) * 100,2) PERSENTASE
FROM
DUAL
UNION ALL
select 'LAPORAN REALISASI APBN' report_name,
530 AS seq,
'D. Surplus/Defisit Anggaran' description,
'N' change_sign_flag,
'D' Flag,
'Y' Fin_Flag,
(select sum(pendapatan_dn-belanja) from 
          (select APBN pendapatan_dn FROM lra_apbn_1 WHERE seq = '5'), 
          (select APBN belanja FROM lra_apbn_1 WHERE seq = '265')         
) APBN,
(select sum(pendapatan_dn-belanja) from 
          (select REALIASI_BUN pendapatan_dn FROM lra_apbn_1 WHERE seq = '5'), 
          (select REALIASI_BUN belanja FROM lra_apbn_1 WHERE seq = '265')          
) REALIASI_BUN,
(select sum(pendapatan_dn-belanja) from 
          (select REALIASI_KPP pendapatan_dn FROM lra_apbn_1 WHERE seq = '5'), 
          (select REALIASI_KPP belanja FROM lra_apbn_1 WHERE seq = '265')
) REALIASI_KPP,
(select sum(pendapatan_dn-belanja) from 
          (select JUMALAH pendapatan_dn FROM lra_apbn_1 WHERE seq = '5'), 
          (select JUMALAH belanja FROM lra_apbn_1 WHERE seq = '265')
) JUMALAH,
ROUND((select sum(pendapatan_dn-belanja) from 
          (select JUMALAH pendapatan_dn FROM lra_apbn_1 WHERE seq = '5'), 
          (select JUMALAH belanja FROM lra_apbn_1 WHERE seq = '265')
) /
(select sum(pendapatan_dn-belanja) from 
          (select APBN pendapatan_dn FROM lra_apbn_1 WHERE seq = '5'), 
          (select APBN belanja FROM lra_apbn_1 WHERE seq = '265')
) * 100,2) PERSENTASE
FROM
DUAL
order by SEQ" 
				
				;
      

        //$sql .= " ORDER BY A.KPPN, A.AKUN ";

        //var_dump($sql);
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
			$d_data->set_deskripsi($val['DESCRIPTION']);
            $d_data->set_apbn($val['APBN']);
            $d_data->set_realisasi_bun($val['REALIASI_BUN']);
            $d_data->set_realisasi_kppn($val['REALIASI_KPP']);
            $d_data->set_jumlah($val['JUMALAH']); 
			$d_data->set_persentase(number_format($val['PERSENTASE'],2,","," "));
			
			$d_data->set_flag($val['FLAG']);				
            $data[] = $d_data;
        }
        return $data;
    }
	
	public function get_lra_yoy_non_filter($filter) {
        Session::get('id_user');
        $sql = " SELECT * FROM LRA_HARIAN_YOY";
        $result = $this->db->select($sql);
        $data = array();
        foreach ($result as $val) {
            $d_data = new $this($this->registry);
			$d_data->set_deskripsi($val['DESCRIPTION']);
            $d_data->set_apbn_14($val['APBN14']);
			$d_data->set_apbn_15($val['APBN15']);
            $d_data->set_jumlah_14($val['JUMALAH_14']); 
			$d_data->set_jumlah_15($val['JUMALAH_15']); 
			$d_data->set_persentase_14($val['PERSENTASE_14']);
			$d_data->set_persentase_15($val['PERSENTASE_15']);
			$d_data->set_flag($val['FLAG']);	
			$d_data->set_updownflag($val['UPDOWN_FLAG']);
            $data[] = $d_data;
        }
        return $data;
    }
	

    /*
     * setter
     */

    public function set_deskripsi($deskripsi) {
        $this->_deskripsi = $deskripsi;
    }

    public function set_apbn($apbn) {
        $this->_apbn = $apbn;
    }

    public function set_realisasi_bun($realisasi_bun) {
        $this->_realisasi_bun = $realisasi_bun;
    }

    public function set_realisasi_kppn($realisasi_kppn) {
        $this->_realisasi_kppn = $realisasi_kppn;
    }

    public function set_jumlah($jumlah) {
        $this->_jumlah = $jumlah;
    }
	public function set_flag($flag) {
        $this->_flag = $flag;
    }
	public function set_updownflag($updownflag) {
        $this->_updownflag = $updownflag;
    }
	public function set_persentase($persentase) {
        $this->_persentase = $persentase;
    }
	public function set_apbn_15($apbn_15) {
        $this->_apbn_15 = $apbn_15;
    }
	public function set_apbn_14($apbn_14) {
        $this->_apbn_14 = $apbn_14;
    }
	public function set_jumlah_15($jumlah_15) {
        $this->_jumlah_15 = $jumlah_15;
    }
	public function set_jumlah_14($jumlah_14) {
        $this->_jumlah_14 = $jumlah_14;
    }
	public function set_persentase_14($persentase_14) {
        $this->_persentase_14 = $persentase_14;
    }
	public function set_persentase_15($persentase_15) {
        $this->_persentase_15 = $persentase_15;
    }
    /*
     * getter
     */
	
	public function get_deskripsi() {
        return $this->_deskripsi;
    }
	
    public function get_apbn() {
        return $this->_apbn;
    }

    public function get_realisasi_bun() {
        return $this->_realisasi_bun;
    }

    public function get_realisasi_kppn() {
        return $this->_realisasi_kppn;
    }

    public function get_jumlah() {
        return $this->_jumlah;
    }
	
	public function get_persentase() {
        return $this->_persentase;
    }
	
    public function get_table1() {
        return $this->_table1;
    }
	public function get_flag() {
        return $this->_flag;
    }
	public function get_updownflag() {
        return $this->_updownflag;
    }
	public function get_apbn_15() {
        return $this->_apbn_15;
    }
	public function get_apbn_14() {
        return $this->_apbn_14;
    }
	public function get_jumlah_14() {
        return $this->_jumlah_14;
    }
	public function get_jumlah_15() {
        return $this->_jumlah_15;
    }
	public function get_persentase_14() {
        return $this->_persentase_14;
    }
	public function get_persentase_15() {
        return $this->_persentase_15;
    }

    /*
     * destruktor
     */

    public function __destruct() {
        
    }

}
